﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;
using System.Globalization;

namespace LdExpress
{
    public class GoodPrices
    {
        public long ID = -1;
        public string PriceNotices = "";
        public string Description = "";
        public int Sort = 0;
        public long UserID = 0;
        public DateTime LastUpdate = DateTime.Now;

        public GoodPrices()
        {
        }

        public GoodPrices(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_GoodPrices";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`PriceNotices`" +
                             ",`Description`" +
                             ",`Sort`" +
                             ",`UserID`" +
                              ",`LastUpdate`" +
                             ") " +
                         " values(" +
                             "'" + DBModule.RefineString(PriceNotices) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + Sort.ToString() +
                             "," + UserID.ToString() +
                              "," + DBModule.RefineDatetime(LastUpdate) +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //GV.Response.Write(strSQL);
                UserObjectsLog.SaveObjectActionLog("goodprices", "add", cn, trans);               
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             "PriceNotices='" + DBModule.RefineString(PriceNotices) + "'" +
                             ",Description='" + DBModule.RefineString(Description) + "'" +
                             ",Sort = " + Sort.ToString() +
                             ",UserID = " + UserID.ToString() +
                              ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("goodprices", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_GoodPrices WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("goodprices", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_GoodPrices WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("PriceNotices")) PriceNotices = dr["PriceNotices"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("UserID")) UserID = int.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }

        public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_GoodPrices where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += strIDs;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_GoodPrices where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
    //GoodPrice Notices
    public class GoodPricesNotice
    {
        public long ID = 0;
        public string POL = "";
        public string POD = "";
        public string CARRNAME = "";
        public float RATE = 0;
        public string DOC = "";
        public string CONT = "";
        public DateTime DateFrom = DateTime.MinValue;
        public DateTime DateTo = DateTime.MinValue;
        public DateTime LastUpdate = DateTime.Now;
        public string Note = "";
        public long UserID = 0;

        public GoodPricesNotice()
        {
        }

        public GoodPricesNotice(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_goodprice_notices";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`POL`" +
                             ",`POD`" +
                             ",`CARRNAME`" +
                             ",`RATE`" +
                             ",`DOC`" +
                             ",`CONT`" +
                             ",`DateFrom`" +
                             ",`DateTo`" +
                             ",`LastUpdate`" +
                             ",`Note`" +
                             ",`UserID`" +
                             ") " +
                         " values(" +
                             "'" + DBModule.RefineString(POL) + "'" +
                             "," + "'" + DBModule.RefineString(POD) + "'" +
                             "," + "'" + DBModule.RefineString(CARRNAME) + "'" +
                             "," + RATE.ToString() +
                             "," + "'" + DBModule.RefineString(DOC) + "'" +
                             "," + "'" + DBModule.RefineString(CONT) + "'" +
                             "," + DBModule.RefineDatetime(DateFrom) +
                             "," + DBModule.RefineDatetime(DateTo) +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             "," + "'" + DBModule.RefineString(Note) + "'" +
                             "," + UserID.ToString() +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             "POL='" + DBModule.RefineString(POL) + "'" +
                             ",POD='" + DBModule.RefineString(POD) + "'" +
                             ",CARRNAME='" + DBModule.RefineString(CARRNAME) + "'" +
                             ",RATE = " + RATE.ToString() +
                             ",DOC='" + DBModule.RefineString(DOC) + "'" +
                             ",CONT='" + DBModule.RefineString(CONT) + "'" +
                             ",DateFrom=" + DBModule.RefineDatetime(DateFrom) + "" +
                             ",DateTo=" + DBModule.RefineDatetime(DateTo) + "" +
                             ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                             ",Note='" + DBModule.RefineString(Note) + "'" +
                             ",UserID = " + UserID.ToString() +

                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_goodprice_notices WHERE ID = " + this.ID.ToString();

            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_goodprice_notices WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("POL")) POL = dr["POL"].ToString();
                if (!dr.IsNull("POD")) POD = dr["POD"].ToString();
                if (!dr.IsNull("CARRNAME")) CARRNAME = dr["CARRNAME"].ToString();
                if (!dr.IsNull("RATE")) RATE = float.Parse(dr["RATE"].ToString());
                if (!dr.IsNull("DOC")) DOC = dr["DOC"].ToString();
                if (!dr.IsNull("CONT")) CONT = dr["CONT"].ToString();
                if (!dr.IsNull("DateFrom")) DateFrom = DateTime.Parse(dr["DateFrom"].ToString());
                if (!dr.IsNull("DateTo")) DateTo = DateTime.Parse(dr["DateTo"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("Note")) Note = dr["Note"].ToString();
                if (!dr.IsNull("UserID")) UserID = long.Parse(dr["UserID"].ToString());
            }
        }

        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_goodprice_notices where 1=1 AND DATE_ADD(datefrom,INTERVAL 31 DAY) > CURRENT_DATE() ";
            if (strCondition.Length > 0)
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_goodprice_notices where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static void PublicGoodPriceNotice(Prices objPrice, string inPutStrDate, string inPutStrRate, string inputCont)
        {

            try
            {
                System.Web.SessionState.HttpSessionState Session = System.Web.HttpContext.Current.Session;
                DateTime DATEFROM = DateTime.ParseExact(inPutStrDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                if (DATEFROM.AddDays(7) >= DateTime.Now)
                {
                    GoodPricesNotice gpn = new GoodPricesNotice();
                    gpn.ID = 0;
                    gpn.CARRNAME = objPrice.CarrName;
                    gpn.POL = objPrice.POLName;
                    gpn.POD = objPrice.PODName;
                    gpn.DOC = "MBL";
                    gpn.CONT = inputCont;
                    gpn.DateFrom = DATEFROM;
                    DateTime DATETO = DateTime.MinValue;
                    if (DATEFROM.Day >= 8 && DATEFROM.Day < 23)// Ngay cuoi thang
                    {
                        DATEFROM = DATEFROM.AddMonths(1);
                        DATETO = DATEFROM.AddDays(-(DATEFROM.Day));
                    }
                    else
                    {
                        if (DATEFROM.Day >= 24 && DATEFROM.Day <= 31)
                        {
                            DATEFROM = DATEFROM.AddMonths(1);
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                        }
                        else
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                    }
                    gpn.DateTo = DATETO;
                    try
                    {
                        gpn.RATE = float.Parse(inPutStrRate.Replace("+", ""));

                    }
                    catch { }
                    gpn.Note = objPrice.Validity.IndexOf("_") > 0 ? objPrice.Validity.Split('_')[0] : "";
                    gpn.Note += objPrice.Description.IndexOf("_") > 0 ? " (" + objPrice.Description.Split('_')[0] + ")" : "";
                    gpn.UserID = long.Parse(Session["logined_id"].ToString());
                    gpn.LastUpdate = DateTime.Now;
                    gpn.Save(GV.cn, GV.trans);
                }
            }
            catch { }
        }
        public static void PublicGoodPriceNoticeFromAreas(PriceAreas objPrice, string inPutStrDate, string inPutStrRate, string inputCont)
        {

            try
            {
                System.Web.SessionState.HttpSessionState Session = System.Web.HttpContext.Current.Session;
                DateTime DATEFROM = DateTime.ParseExact(inPutStrDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                if (DATEFROM.AddDays(7) >= DateTime.Now)
                {
                    GoodPricesNotice gpn = new GoodPricesNotice();
                    gpn.ID = 0;
                    gpn.CARRNAME = objPrice.CarrName;
                    gpn.POL = objPrice.ServiceAreaName;
                    gpn.POD = objPrice.ServiceAreaName;
                    if (objPrice.PolName.Length > 0)
                        gpn.POL = objPrice.PolName;
                    if (objPrice.PodName.Length > 0)
                        gpn.POD = objPrice.PodName;
                    gpn.DOC = "MBL";
                    gpn.CONT = inputCont;
                    gpn.DateFrom = DATEFROM;
                    DateTime DATETO = DateTime.MinValue;
                    if (DATEFROM.Day >= 8 && DATEFROM.Day < 23)// Ngay cuoi thang
                    {
                        DATEFROM = DATEFROM.AddMonths(1);
                        DATETO = DATEFROM.AddDays(-(DATEFROM.Day));
                    }
                    else
                    {
                        if (DATEFROM.Day >= 24 && DATEFROM.Day <= 31)
                        {
                            DATEFROM = DATEFROM.AddMonths(1);
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                        }
                        else
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                    }
                    gpn.DateTo = DATETO;
                    try
                    {
                        gpn.RATE = float.Parse(inPutStrRate.Replace("+", ""));

                    }
                    catch { }
                    gpn.Note = objPrice.Validity.IndexOf("_") > 0 ? objPrice.Validity.Split('_')[0] : "";
                    gpn.Note += objPrice.Note.IndexOf("_") > 0 ? " (" + objPrice.Note.Split('_')[0] + ")" : "";
                    gpn.UserID = long.Parse(Session["logined_id"].ToString());
                    gpn.LastUpdate = DateTime.Now;
                    gpn.Save(GV.cn, GV.trans);
                }
            }
            catch { }
        }

        public static void PublicGoodPriceNoticeFromAreasNew(PriceAreasNew objPrice, string inPutStrDate, string inPutStrRate, string inputCont)
        {

            try
            {
                System.Web.SessionState.HttpSessionState Session = System.Web.HttpContext.Current.Session;
                DateTime DATEFROM = DateTime.ParseExact(inPutStrDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                if (DATEFROM.AddDays(7) >= DateTime.Now)
                {
                    GoodPricesNotice gpn = new GoodPricesNotice();
                    gpn.ID = 0;
                    gpn.CARRNAME = objPrice.CarrName;
                    gpn.POL = objPrice.PolName;
                    gpn.POD = objPrice.PodIncluded.IndexOf("_") > 0 ? objPrice.PodIncluded.Split('_')[0] : "";
                    gpn.DOC = "MBL";
                    gpn.CONT = inputCont;
                    gpn.DateFrom = DATEFROM;
                    DateTime DATETO = DateTime.MinValue;
                    if (DATEFROM.Day >= 8 && DATEFROM.Day < 23)// Ngay cuoi thang
                    {
                        DATEFROM = DATEFROM.AddMonths(1);
                        DATETO = DATEFROM.AddDays(-(DATEFROM.Day));
                    }
                    else
                    {
                        if (DATEFROM.Day >= 24 && DATEFROM.Day <= 31)
                        {
                            DATEFROM = DATEFROM.AddMonths(1);
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                        }
                        else
                            DATETO = new DateTime(DATEFROM.Year, DATEFROM.Month, 14);
                    }
                    gpn.DateTo = DATETO;
                    try
                    {
                        gpn.RATE = float.Parse(inPutStrRate.Replace("+", ""));

                    }
                    catch { }
                    //gpn.Note = objPrice.Validity.IndexOf("_") > 0 ? objPrice.Validity.Split('_')[0] : "";
                    //gpn.Note += objPrice.Note.IndexOf("_") > 0 ? " (" + objPrice.Note.Split('_')[0] + ")" : "";
                    gpn.UserID = long.Parse(Session["logined_id"].ToString());
                    gpn.LastUpdate = DateTime.Now;
                    gpn.Save(GV.cn, GV.trans);
                }
            }
            catch { }
        }
    }
}

